﻿using System;
using System.Collections.Generic;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GIMS.MODEL;
using System.Data;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_StockMovement
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_Requisition_Desc> GetListForCutStock(string requisition_code)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_Requisition_Desc> infoList = new List<M_Requisition_Desc>();
            try
            {
                dbManager.Open();

                strSQL =  " SELECT req_desc.requisiton_des_item,req_desc.requisition_code,requisition_item,req_desc.quantity,req_desc.icm_res_item_seq "
                        + " ,concat(coalesce(icm_res_item_code, ''), '  ', coalesce(icm_res_item_name, '')) as icm_res_item_name "
                        + " ,job_site_seq,req_desc.icm_res_item_seq,cutstock_real_quantity,st.movement_code "
                        + " FROM icm_requisition_desc req_desc "
                        + " LEFT JOIN icm_stock_movement st ON st.icm_res_item_seq = req_desc.icm_res_item_seq "
                        + " LEFT JOIN icm_receive rc ON st.icm_receive_seq = rc.icm_receive_seq "
                        + " LEFT JOIN icm_mas_resource_item rt ON req_desc.icm_res_item_seq = rt.icm_res_item_seq "
                        + " LEFT JOIN icm_requisition rq ON req_desc.requisition_code = rq.requisition_code "
                        + " WHERE req_desc.requisition_code LIKE '%' '" + requisition_code + "'  '%'  and req_desc.record_status<>'D' "
                        + " ORDER BY receive_date,rc.icm_receive_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_Requisition_Desc info = new M_Requisition_Desc();
                        info.requisiton_des_item = cn.ConvertNullInt(drc[i]["requisiton_des_item"]);
                        info.requisition_code = drc[i]["requisition_code"].ToString();
                        info.requisition_item = Convert.ToInt32(drc[i]["requisition_item"]);
                        info.quantity = Convert.ToDouble(drc[i]["quantity"]);
                        info.icm_res_item_seq = Convert.ToInt32(drc[i]["icm_res_item_seq"]);
                        info.job_site_seq = cn.ConvertNullInt(drc[i]["job_site_seq"]);
                        info.icm_res_item_name = drc[i]["icm_res_item_name"].ToString();
                        info.cutstock_real_quantity = cn.ConvertNullDouble(drc[i]["cutstock_real_quantity"]);
                        info.movement_code = drc[i]["movement_code"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_IcmReceiveObject> GetListFIFOInventoryReceive(Nullable<Int32> icm_res_item_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmReceiveObject> infoList = new List<M_IcmReceiveObject>();
            try
            {
                dbManager.Open();

                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,rc.icm_res_item_seq,quantity,quantity_remain "
                         + " ,quantity_remain_before ,quantity_remain_after,receive_date "
                         +" ,concat(COALESCE(ritem.icm_res_item_code,''),':',COALESCE(ritem.icm_res_item_name,'')) as icm_res_item_name "
                         +" FROM icm_receive rc "
                         +" LEFT JOIN icm_mas_resource_item ritem ON rc.icm_res_item_seq = ritem.icm_res_item_seq "
                         +" WHERE rc.record_status <>'D' and quantity_remain_after > 0 "
                         +" and rc.icm_res_item_seq= "+icm_res_item_seq
                         + " ORDER BY rc.receive_date,icm_receive_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmReceiveObject info = new M_IcmReceiveObject();
                        info.icm_receive_seq = Convert.ToInt32(drc[i]["icm_receive_seq"]);
                        info.icm_receive_code = drc[i]["icm_receive_code"].ToString();
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.quantity = Convert.ToDouble(drc[i]["quantity"]);
                        info.quantity_remain = Convert.ToDouble(drc[i]["quantity_remain"]);
                        info.quantity_remain_after = Convert.ToDouble(drc[i]["quantity_remain_after"]);
                        info.quantity_remain_before = Convert.ToDouble(drc[i]["quantity_remain_before"]);
                        //info.icm_res_item_seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"]);
                        info.icm_res_item_name = drc[i]["icm_res_item_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public DataTable GetListDataTableFIFOInventoryReceive(Nullable<Int32> icm_res_item_seq)
        {
            string strSQL = "";
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,rc.icm_res_item_seq,quantity,quantity_remain "
                          + " ,quantity_remain_before ,quantity_remain_after,receive_date "
                          + " ,concat(COALESCE(ritem.icm_res_item_code,''),':',COALESCE(ritem.icm_res_item_name,'')) as icm_res_item_name "
                          + " FROM icm_receive rc "
                          + " LEFT JOIN icm_mas_resource_item ritem ON rc.icm_res_item_seq = ritem.icm_res_item_seq "
                          + " WHERE rc.record_status <>'D' and quantity_remain_after > 0 "
                          + " and rc.icm_res_item_seq= " + icm_res_item_seq
                          + " ORDER BY rc.receive_date,icm_receive_seq ";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public M_IcmReceiveObject GetListFIFOFillBySeq(Nullable<Int32> icm_res_item_seq,Nullable<Int32> icm_receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmReceiveObject info = new M_IcmReceiveObject();
            try
            {
                dbManager.Open();
                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,rc.icm_res_item_seq,quantity,quantity_remain "
                         + " ,quantity_remain_before ,quantity_remain_after "
                         + " ,concat(COALESCE(ritem.icm_res_item_code,''),':',COALESCE(ritem.icm_res_item_name,'')) as icm_res_item_name "
                         + " FROM icm_receive rc "
                         + " LEFT JOIN icm_mas_resource_item ritem ON rc.icm_res_item_seq = ritem.icm_res_item_seq "
                         + " WHERE rc.record_status <>'D' and quantity_remain_after > 0 "
                         + " and rc.icm_res_item_seq= " + icm_res_item_seq
                         + " and rc.icm_receive_seq = " + icm_receive_seq
                         + " ORDER BY rc.receive_date,icm_receive_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_receive_seq = Convert.ToInt32(drc[0]["icm_receive_seq"]);
                    info.icm_receive_code = drc[0]["icm_receive_code"].ToString();
                    info.quantity = Convert.ToDouble(drc[0]["quantity"]);
                    info.quantity_remain = Convert.ToDouble(drc[0]["quantity_remain"]);
                    info.quantity_remain_after = Convert.ToDouble(drc[0]["quantity_remain_after"]);
                    info.quantity_remain_before = Convert.ToDouble(drc[0]["quantity_remain_before"]);
                    //info.icm_res_item_seq = cn.ConvertNullInt(drc[0]["icm_res_item_seq"]);
                    info.icm_res_item_name = drc[0]["icm_res_item_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
        public M_IcmReceiveObject GetListFIFOIncreaseZeroFillBySeq(Nullable<Int32> icm_res_item_seq, Nullable<Int32> icm_receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmReceiveObject info = new M_IcmReceiveObject();
            try
            {
                dbManager.Open();
                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,rc.icm_res_item_seq,quantity,quantity_remain "
                         + " ,quantity_remain_before ,quantity_remain_after "
                         + " ,concat(COALESCE(ritem.icm_res_item_code,''),':',COALESCE(ritem.icm_res_item_name,'')) as icm_res_item_name "
                         + " FROM icm_receive rc "
                         + " LEFT JOIN icm_mas_resource_item ritem ON rc.icm_res_item_seq = ritem.icm_res_item_seq "
                         + " WHERE rc.record_status <>'D' "
                         + " and rc.icm_res_item_seq= " + icm_res_item_seq
                         + " and rc.icm_receive_seq = " + icm_receive_seq
                         + " ORDER BY rc.receive_date,icm_receive_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_receive_seq = Convert.ToInt32(drc[0]["icm_receive_seq"]);
                    info.icm_receive_code = drc[0]["icm_receive_code"].ToString();
                    info.quantity = Convert.ToDouble(drc[0]["quantity"]);
                    info.quantity_remain = Convert.ToDouble(drc[0]["quantity_remain"]);
                    info.quantity_remain_after = Convert.ToDouble(drc[0]["quantity_remain_after"]);
                    info.quantity_remain_before = Convert.ToDouble(drc[0]["quantity_remain_before"]);
                    //info.icm_res_item_seq = cn.ConvertNullInt(drc[0]["icm_res_item_seq"]);
                    info.icm_res_item_name = drc[0]["icm_res_item_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public double CheckQuantityCutStock(Nullable<Int32> icm_res_item_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@icm_res_item_seq", icm_res_item_seq);
            double dQuantity = 0;
            try
            {
                dbManager.Open();
                strSQL = " SELECT sum(quantity_remain_after) as sum_quantity "
                         +" FROM icm_receive rc "
                         +" LEFT JOIN icm_mas_resource_item ritem ON rc.icm_res_item_seq = ritem.icm_res_item_seq "
                         +" WHERE rc.record_status <>'D' and quantity_remain_after > 0 "
                         + " and rc.icm_res_item_seq=@icm_res_item_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    dQuantity = Convert.ToDouble(drc[0]["sum_quantity"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dQuantity;
        }


        public M_StockMovement GenerateMovementCode()
        {
            string strSQL;
            DataRowCollection drc = null;
            M_StockMovement info = new M_StockMovement();
            try
            {
                dbManager.Open();

                strSQL =  " SELECT case when  (Year(Now()) <2500) then Year(Now())+543 else Year(Now()) end YearValue , Month(Now()) as MonthValue "
                         +" ,COUNT(movement_code)+1 as running  "
                         +" FROM icm_stock_movement "
                         +" WHERE YEAR(created_when) = YEAR(Now()) and  Month(created_when) = Month(Now()) ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.str_year = drc[0]["YearValue"].ToString();
                    info.str_month = drc[0]["MonthValue"].ToString();
                    info.movement_running = drc[0]["running"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public Int32 CheckEditValue(Nullable<Int32> requisiton_des_item, Nullable<Int32> icm_res_item_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            Int32 pCheckCount = 0;
            try
            {
                dbManager.Open();

                strSQL =  "SELECT COUNT(stock_movement_seq) as num "
                         +" FROM icm_stock_movement "
                         +" WHERE requisiton_des_item= "+ requisiton_des_item +" and icm_res_item_seq= "+icm_res_item_seq;

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    pCheckCount = Convert.ToInt32(drc[0]["num"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return pCheckCount;
        }

        public void Insert_StockMovement(M_StockMovement info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@movement_code", info.movement_code);
                dbManager.AddParameters(1, "@icm_receive_seq", info.icm_receive_seq);
                dbManager.AddParameters(2, "@to_job_site_seq", info.to_job_site_seq);
                dbManager.AddParameters(3, "@icm_res_item_seq", info.icm_res_item_seq);
                dbManager.AddParameters(4, "@requisiton_des_item", info.requisiton_des_item);
                dbManager.AddParameters(5, "@request_quantity", info.request_quantity);
                dbManager.AddParameters(6, "@cutstock_real_quantity", info.cutstock_real_quantity);
                dbManager.AddParameters(7, "@created_by", info.created_by);
                dbManager.AddParameters(8, "@created_when", info.created_when);

                strSQL =  " INSERT INTO icm_stock_movement "
                        + " (movement_code,icm_receive_seq,to_job_site_seq,icm_res_item_seq,requisiton_des_item,request_quantity,cutstock_real_quantity,created_by,created_when) "
                        + " VALUES "
                        + " (@movement_code,@icm_receive_seq,@to_job_site_seq,@icm_res_item_seq,@requisiton_des_item,@request_quantity,@cutstock_real_quantity,@created_by,@created_when) ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update_StockMovement(M_StockMovement info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@movement_code", info.movement_code);
                dbManager.AddParameters(1, "@cutstock_real_quantity", info.cutstock_real_quantity);
                dbManager.AddParameters(2, "@created_by", info.created_by);
                dbManager.AddParameters(3, "@created_when", info.created_when);
                dbManager.AddParameters(4, "@icm_receive_seq", info.icm_receive_seq);

                strSQL = " UPDATE icm_stock_movement "
                        + " SET cutstock_real_quantity = @cutstock_real_quantity "
                        + " ,created_by =@created_by "
                        + " ,created_when = @created_when "
                        + " ,icm_receive_seq = @icm_receive_seq "
                        + " WHERE movement_code = @movement_code ";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
